"""
Case Type   : connect by
Case Name   : mot表结合connect by子句
Create At   : 2023/1/3
Owner       : opentestcase009
Description :
    1、检查参数，修改配置，并重启数据库;
    2、创建内存表;
    3、查询以e_id为1开始的节点的直属节点
    4、结合层次递归查询函数查看递归层级
    5、清理环境
Expect      :
    1、修改成功，重启数据库成功;
    2、创建内存表成功;
    3、查看成功
    4、查看成功
    5、清理环境成功
History     :
    modified by nanyang12 2025/3/18: 研发代码变更，增加motserver开关配置
"""

import os
import unittest
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class MOTTest(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} 开始执行-----')
        self.constant = Constant()
        self.node = CommonSH('PrimaryDbUser')
        self.table = 't_connectby_010'
        self.config_item = "enable_incremental_checkpoint"

    def test_mot_isolation(self):
        text = '-----step1:检查参数，修改配置，并重启数据库   expect:成功-----'
        self.log.info(text)
        res = self.node.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            f'enable_mot_server=on')
        self.assertTrue(res)
        check_res = self.node.execut_db_sql(
            f'''show {self.config_item};''')
        if 'off' not in check_res.splitlines()[-2].strip():
            self.node.execute_gsguc('set',
                                    self.constant.GSGUC_SUCCESS_MSG,
                                    f'''{self.config_item}=off''')
        self.node.restart_db_cluster()
        result = self.node.get_db_cluster_status()
        self.log.info(result)
        self.assertTrue("Degraded" in result or "Normal" in result,
                        '执行失败' + text)

        text = '-----step2:创建内存表,插入数据   expect:成功-----'
        self.log.info(text)
        create_cmd = f'''drop foreign table if exists {self.table};
            create foreign table {self.table}
            (e_id int,e_name varchar(10),l_id int);
            insert into {self.table} values(1,'arise',0);
            insert into {self.table} values(2,'jack',1);
            insert into {self.table} values(3,'judy',1);
            insert into {self.table} values(4,'lion',1);
            insert into {self.table} values(5,'scott',2);
            insert into {self.table} values(6,'tiger',3);
            insert into {self.table} values(7,'king',2);'''
        self.log.info(create_cmd)
        create_res = self.node.execut_db_sql(create_cmd)
        self.log.info(create_res)
        self.assertTrue(self.constant.CREATE_FOREIGN_SUCCESS_MSG in create_res
                        and self.constant.INSERT_SUCCESS_MSG in create_res)

        text = "-----step3:查询以e_id为1开始的节点的直属节点 expect:成功-----"
        self.log.info(text)
        select_cmd1 = f'''select * from {self.table} start with e_id = 1 
            connect by nocycle prior e_id = l_id order by e_id asc;'''
        self.log.info(select_cmd1)
        select_res1 = self.node.execut_db_sql(select_cmd1)
        self.log.info(select_res1)
        assert_res1 = select_res1.splitlines()[-2].split('|')[0].strip()
        self.assertEqual('7', assert_res1, '执行成功：' + text)

        text = '-----step4:结合层次递归查询函数查看递归层级  expect:成功-----'
        self.log.info(text)
        select_cmd2 = f'''select sys_connect_by_path(e_name,'-') as path, *, 
            level from {self.table} start with e_id = 1 
            connect by nocycle prior e_id = l_id;'''
        self.log.info(select_cmd2)
        select_res2 = self.node.execut_db_sql(select_cmd2)
        self.log.info(select_res2)
        assert_res2 = select_res2.splitlines()[-2].split('|')[0].strip()
        self.assertEqual('-arise-jack-scott', assert_res2, '执行成功：' + text)

    def tearDown(self):
        text = '-----step5:恢复参数,清理环境;   expect:成功-----'
        self.log.info(text)
        drop_cmd = f'''drop foreign table {self.table} cascade;
            checkpoint;'''
        drop_res = self.node.execut_db_sql(drop_cmd)
        self.log.info(drop_res)

        self.log.info('------检查参数,恢复配置------')
        res = self.node.execute_gsguc(
            'set', self.constant.GSGUC_SUCCESS_MSG,
            f'enable_mot_server=off')
        self.assertTrue(res)
        check_res = self.node.execut_db_sql(f'show {self.config_item};')
        self.log.info(check_res)
        self.res = self.node.execute_gsguc('set',
                                           self.constant.GSGUC_SUCCESS_MSG,
                                           f'{self.config_item} = on')

        self.log.info('------重启数据库------')
        restart = self.node.restart_db_cluster()
        self.log.info(restart)
        status = self.node.get_db_cluster_status()
        self.log.info(status)
        self.assertTrue("Degraded" in status or "Normal" in status,
                        '执行失败' + text)
        self.assertTrue(self.constant.DROP_FOREIGN_SUCCESS_MSG in drop_res
                        and self.constant.CHECKPOINT_SUCCESS_MSG in drop_res,
                        '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} 执行完成-----')
